package com.music.mapper.music;

import com.alibaba.fastjson.JSONObject;
import com.music.dto.basic.UserDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface SelfCenterMapper {
    @Select("SELECT user_id, login_name, login_pwd, user_name, user_sex, user_info, image_url, gmt_create,\n" +
            "(SELECT count(*) FROM music_playlist a LEFT JOIN music_song b ON a.playlist_id = b.playlist_id WHERE user_id = #{userId}) upload, \n" +
            "(SELECT count(*) FROM music_download WHERE user_id = #{userId}) download,\n" +
            "(SELECT count(*) FROM collection_song WHERE user_id = #{userId}) collect\n" +
            "FROM basic_user\n" +
            "WHERE user_id = #{userId}")
    UserDTO getInfo(@Param("userId")Long userId) throws Exception;

    @Select("select singer_name FROM music_download a left join music_song b on a.song_id = b.song_id\n" +
            "WHERE user_id = #{userId} " +
            "and download_status = 1 ")
    List<String> getSingerName(@Param("userId")Long userId) throws Exception;

    @Select("SELECT song_name songName, count(*) count FROM\n" +
            "(SELECT a.song_id,song_name FROM music_download a LEFT JOIN \n" +
            "(SELECT a.playlist_id, song_id, song_name,user_id FROM music_playlist a right JOIN music_song b on a.playlist_id = b.playlist_id) b \n" +
            "on a.song_id = b.song_id\n" +
            "where b.user_id = #{userId}) a\n" +
            "GROUP BY song_name\n" +
            "ORDER BY count desc " +
            "LIMIT 5")
    List<JSONObject> getDownloadCount(@Param("userId")Long userId) throws Exception;
}
